package hk.polyu;


import java.util.*;
import java.io.*;
import java.sql.*;




/**
 * Wrapper class encapsulating all operations on database
 * If you want to replace PostgreSQL with different databases,
 * Just re-implement this file.
 * @author hezongjian
 *
 */
public class DBWrapper {
	
	// Singleton pattern
	private static DBWrapper inst = null;
	private DBWrapper()
	{
	}
	public static DBWrapper instance()
	{
		if (inst == null)
		{
			inst = new DBWrapper();
			inst.load();
		}
		return inst;
	}
	// end singleton

	
	// connection URL, assume database exist
	private final String mConnectionURL = "jdbc:postgresql://imcl.comp.polyu.edu.hk:5432/wheelchairDB";
	private final String mUserName = "wheelchair";
	private final String mPassword = "uio";
	
	private Connection mConn = null;
	
	
	private void load()
	{
	}
	
	private void unload()
	{
	}
	
	private boolean connect()
	{
		try {
			this.mConn = DriverManager.getConnection(mConnectionURL, mUserName, mPassword);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}
	
	private boolean disconnect()
	{
		try {
			this.mConn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
		this.mConn = null;
		}
		return true;
	}
	
	
	// for test only
	public void appendRandom() {
		this.connect();

		Statement st = null;
		ResultSet rs = null;

		try {
			st = this.mConn.createStatement();
			rs = st.executeQuery("SELECT * FROM people");

			while (rs.next()) {
				System.out.println(rs.getString(2));
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		this.disconnect();
	}
	
	
	// append current log into database
	public void appendLog(SensorData d)
	{
		
	}
	
	public ArrayList<SensorData> queryLog()
	{
		String sqlString = "SELECT * FROM LOG ORDER BY SampleDate";
		
		Statement  s;
		ResultSet rs;
		ArrayList<SensorData> ret = null;
		
		try {
			this.connect();
			// create the statement
			s = mConn.createStatement();
			// execute
			rs = s.executeQuery(sqlString);
			
			// create return value
			ret = new ArrayList<SensorData>();
			
			// traverse
			while(rs.next())
			{
				int tid = rs.getInt(1);
				int nid = rs.getInt(2);
				double temp = rs.getDouble(3);
				java.util.Date samp =  new java.util.Date(rs.getTimestamp(4).getTime());
				java.util.Date dload =  new java.util.Date(rs.getTimestamp(5).getTime());
				
				SensorData md = new SensorData(1, 1.0, samp);
				ret.add(md);
			}
			
			// close connection
			rs.close();
			this.disconnect();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;

	}
	

	
	
	public void loadSetting()
	{
		String sqlString = "SELECT * FROM SETTING";
		
		Statement  s;
		
		ResultSet rs;
		
		try {
			this.connect();
			// create the statement
			s = mConn.createStatement();
			// execute
			rs = s.executeQuery(sqlString);
			
			// assume length > 1; query the first one
			rs.next();
//			Setting.portNumber = rs.getInt(1);
//			Setting.baudRate = rs.getInt(2);
//			Setting.warningTemp = rs.getInt(3);
//			Setting.errorTemp = rs.getInt(4);
			rs.close();
			
			// close connection
			this.disconnect();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// save the setting to database
	public void saveSetting()
	{
		String sqlString = "UPDATE SETTING SET PortNumber=?,BaudRate=?,WarnTemp=?,ErrTemp=?";
		
		PreparedStatement  ps;
		
		try {
			this.mConn = DriverManager.getConnection(mConnectionURL);
			// create the statement
			ps = mConn.prepareStatement(sqlString);
			// replace statement value
//			ps.setInt(1, Setting.portNumber);
//			ps.setInt(2, Setting.baudRate);
//			ps.setInt(3, Setting.warningTemp);
//			ps.setInt(4, Setting.errorTemp);
			
			ps.executeUpdate();
			
			// close connection
			mConn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	}
	


}
